/*  
 * @(#) DBHelper.java Create on 2012-5-8 下午5:51:08   
 *   
 * Copyright 2012 by xl.   
 */

package cn.com.common.base.dao;

import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;

/**
 * 
 * @author zhanghongliang
 * @date 2012-5-8
 */
@Service(value = "sqlHelper")
public class SqlHelper {
	protected Logger log = Logger.getLogger(SqlHelper.class);
	public final static String TABLE_NAME_KEY = "_tableName";

	/**
	 * 取inert sql
	 * 
	 * <pre>
	 * <ul>
	 * 	<li>
	 * 	map中必须有属性 _tableName，作为数据库表名
	 * </li>
	 * 	<li>
	 *  取map中key ，排除：serialVersionUID字段，与 以下划线开始的字段
	 * </li>
	 * 	<li>
	 * 取value!= null 的属性作为新增字段
	 * </li>
	 * </ul>
	 * </pre>
	 * 
	 * @Title: getInsertSql
	 * @data:2012-5-9上午10:00:42
	 * @author:zhanghongliang
	 * 
	 * @param params
	 * @return
	 */
	public String getInsertSql(Map<String, Object> params) {
		if (params == null || params.size() == 0) {
			return null;
		}
		String tableName = (String) params.get(TABLE_NAME_KEY);
		if (StringUtils.isBlank(tableName)) {
			log.warn("map has't _tableName property");
			return null;
		}

		StringBuffer result = new StringBuffer();
		StringBuffer insertKeys = new StringBuffer();
		StringBuffer insertValues = new StringBuffer();

		for (Entry<String, Object> entry : params.entrySet()) {
			String key = entry.getKey();
			Object value = entry.getValue();
			if ("serialVersionUID".equals(key)) {
				continue;
			}
			if ("class".equals(key)) {
				continue;
			}
			if (key.startsWith("_")) {
				continue;
			}
			if ("id".equals(key)) {
				continue;
			}
			if (value != null) {
				insertKeys.append(key + ",");
				insertValues.append(":" + key + ",");
			}
		}

		result.append("INSERT INTO ");
		result.append(" " + tableName);
		result.append(" (" + StringUtils.removeEnd(insertKeys.toString(), ",") + ")");
		result.append(" VALUES (" + StringUtils.removeEnd(insertValues.toString(), ",") + ")");

		return result.toString();
	}

	/**
	 * 取update sql
	 * 
	 * <pre>
	 * 	更新所有字段，根据id
	 * </pre>
	 * 
	 * @Title: getUpdateSql
	 * @data:2012-5-9上午10:52:20
	 * @author:zhanghongliang
	 * 
	 * @param params
	 * @return
	 */
	public String getUpdateSql(Map<String, Object> params) {
		return get_updateSql(params, true);
	}

	/**
	 * 取update sql
	 * 
	 * <pre>
	 * 	更新不为null的字段，根据id
	 * </pre>
	 * 
	 * @Title: getUpdateSql
	 * @data:2012-5-9上午10:52:20
	 * @author:zhanghongliang
	 * 
	 * @param params
	 * @return
	 */
	public String getUpdateNotNullSql(Map<String, Object> params) {
		return get_updateSql(params, false);
	}

	private String get_updateSql(Map<String, Object> params, boolean allFlag) {
		if (params == null || params.size() == 0) {
			return null;
		}
		String tableName = (String) params.get(TABLE_NAME_KEY);
		if (StringUtils.isBlank(tableName)) {
			log.warn("update map has't _tableName property");
			return null;
		}
		if (!params.containsKey("id") || params.get("id") == null) {
			log.warn("update map has't id property");
			return null;
		}

		StringBuffer result = new StringBuffer();
		StringBuffer updateParams = new StringBuffer();

		for (Entry<String, Object> entry : params.entrySet()) {
			String key = entry.getKey();
			Object value = entry.getValue();
			if ("class".equals(key)) {
				continue;
			}
			if ("serialVersionUID".equals(key)) {
				continue;
			}
			if (key.startsWith("_")) {
				continue;
			}
			if ("id".equals(key)) {
				continue;
			}
			// allFlag==true 更新所有字段，false，更新不为null的字段
			if (allFlag || value != null)
				updateParams.append(key + "=:" + key + ",");
		}

		result.append("UPDATE " + tableName + " SET");
		result.append(" " + StringUtils.removeEnd(updateParams.toString(), ","));
		result.append(" WHERE id=:id");

		return result.toString();
	}

	/**
	 * 取分页sql
	 * 
	 * @Title: getFetchSql
	 * @data:2012-5-9下午4:29:43
	 * @author:zhanghongliang
	 * 
	 * @param sql
	 * @param pageNow
	 * @param pageSize
	 * @return
	 */
	public String getFetchSql(String sql, int pageNow, int pageSize) {
		StringBuffer result = new StringBuffer();
		result.append(sql);
		
		if (pageNow > 0 && pageSize > 0 && StringUtils.isNotBlank(sql)) {
			result.append(" LIMIT " + (pageNow - 1) * pageSize + "," + pageSize);
		}
		return result.toString();
	}

	/**
	 * 取list查询 sql
	 * 
	 * @Title: getQueryListSql
	 * @data:2012-5-9下午4:29:58
	 * @author:zhanghongliang
	 * 
	 * @param params
	 * @return
	 */
	public String getQueryListSql(Map<String, Object> params) {
		StringBuffer result = new StringBuffer();
		if (params != null) {
			String tableName = (String) params.get(TABLE_NAME_KEY);
			if (StringUtils.isNotBlank(tableName)) {
				result.append("SELECT * FROM " + tableName + " WHERE 1=1");
			}
		}
		return result.toString();
	}

	/**
	 * 取查询总数 sql
	 * 
	 * @Title: getQueryCountSql
	 * @data:2012-5-9下午4:30:08
	 * @author:zhanghongliang
	 * 
	 * @param params
	 * @return
	 */
	public String getQueryCountSql(Map<String, Object> params) {
		StringBuffer result = new StringBuffer();
		if (params != null) {
			String tableName = (String) params.get(TABLE_NAME_KEY);
			if (StringUtils.isNotBlank(tableName)) {
				result.append("SELECT COUNT(*) FROM " + tableName + " WHERE 1=1");
			}
		}
		return result.toString();
	}

	/**
	 * 取where 条件
	 * 
	 * @Title: getQueryCondtion
	 * @data:2012-5-9下午4:30:19
	 * @author:zhanghongliang
	 * 
	 * @param params
	 * @return
	 */
	public String getQueryCondtion(Map<String, Object> params) {
		StringBuffer result = new StringBuffer();
		if (params != null) {
			for (Entry<String, Object> entry : params.entrySet()) {
				String key = entry.getKey();
				Object value = entry.getValue();
				if ("class".equals(key)) {
					continue;
				}
				if ("serialVersionUID".equals(key)) {
					continue;
				}
				if (TABLE_NAME_KEY.equals(key)) {
					continue;
				}
				if (value == null || StringUtils.isBlank(value.toString()))
					continue;
				if (key.startsWith("_") && key.endsWith("_condtion_GT")) {
					String _key = StringUtils.removeStart(key, "_");
					_key = StringUtils.removeEnd(_key, "_condtion_GT");
					result.append(" AND " + _key + ">=:" + key);
					continue;
				}
				if (key.startsWith("_") && key.endsWith("_condtion_LT")) {
					String _key = StringUtils.removeStart(key, "_");
					_key = StringUtils.removeEnd(_key, "_condtion_LT");
					result.append(" AND " + _key + "<=:" + key);
					continue;
				}

				if(key.startsWith("_"))continue;
				if (value instanceof Number) {
					result.append(" AND " + key + "=:key");
					continue;
				}
				if (value instanceof String) {
					result.append(" AND " + key + " LIKE:" + key);
					params.put(key, "%" + value.toString() + "%");
					continue;
				}
				result.append(" AND " + key + " =:" + key);
			}
		}
		return result.toString();
	}
}
